package com.liuya.db.util;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.liuya.common.StringUtil;
import com.liuya.db.DBPower;
import com.liuya.safe.exception.AuthException;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

/**
* 提供数据库操作的实用方法
* @author liuya
* @version 0.1.0
*/
public class DBUtil implements java.io.Serializable  {
    private static Log log = LogFactory.getLog(DBUtil.class);

    public static final String DB2 = "DB2";
    public static final String ORACLE = "ORACLE";
    public static final String MYSQL = "MYSQL";
    public static final String SQLSERVER = "SQL SERVER";
    public static final String OTHER_DATABASE = "OTHER";

    public static boolean isSQLServer() {
        return DBUtil.getDatabaseProductName(DBPower.getDefaultConnection()).equals(DBUtil.SQLSERVER);
    }

    public static boolean supportsLimit(Connection conn) {
        String dataBase = getDatabaseProductName(conn);
        if (dataBase.equals(DB2) || dataBase.equals(ORACLE) || dataBase.equals(MYSQL)) {
            return true;
        } else {
            return false;
        }
    }

    public static void close(Connection conn) {
        if (conn != null) {
            try {
                if (!conn.isClosed()) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                conn = null;
            }
        }
    }

    public static void close(Statement stmt) {
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                stmt = null;
            }
        }
    }

    public static void close(ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                rs = null;
            }
        }
    }

    public static void close(Statement stmt, Connection conn) {
        close(stmt);
        close(conn);
    }

    public static void close(ResultSet rs, Statement stmt, Connection conn) {
        close(rs);
        close(stmt);
        close(conn);
    }

    public static String insertSql(String tableName, String[] columnNames) {
        StringBuffer buff = new StringBuffer();
        buff.append("INSERT INTO ");
        buff.append(tableName);
        buff.append(" (");
        StringUtil.append(buff, columnNames);
        buff.append(") VALUES(");
        StringUtil.append(buff, columnNames, false, "?", ",");
        buff.append(")");
        return buff.toString();
    }

    public static String selectSql(String tableName, String[] columnNames) {
        StringBuffer buff = new StringBuffer();
        buff.append("SELECT ");
        StringUtil.append(buff, columnNames);
        buff.append(" FROM ");
        buff.append(tableName);
        return buff.toString();
    }

//    public static String selectSql(Table table) {
//        String tableAlias = " t" + table.getId();
//        StringBuffer buff = new StringBuffer();
//        buff.append("SELECT ");
//        buff.append(columnsString(table.getColumns(), tableAlias));
//        buff.append(" FROM ");
//
//        buff.append(table.getName() + tableAlias);
//        return buff.toString();
//    }
//
//    private static StringBuffer columnsString(Column[] columns, String tableAlias) {
//        StringBuffer buff = new StringBuffer();
//        buff.append(columnString(columns[0], tableAlias));
//        for (int i = 1; i < columns.length; i++) {
//            buff.append(",").append(columnString(columns[i], tableAlias));
//        }
//        return buff;
//    }
//
//    private static String columnString(Column column, String tableAlias) {
//        if (column.getFunction() == null || column.getFunction().equals(""))
//            return tableAlias + "." + column.getName();
//        else
//            return column.getFunction() + "(" + tableAlias + "." + column.getName() + ")";
//    }

    public static String updateSql(String tableName, String[] idColumnNames, String[] exceptIdColumnNames) {
        StringBuffer buff = new StringBuffer();
        buff.append("UPDATE ");
        buff.append(tableName);
        buff.append(" SET ");
        StringUtil.append(buff, exceptIdColumnNames, true, "=?", ",");
        buff.append(" WHERE ");
        StringUtil.append(buff, idColumnNames, true, "=?", " AND ");
        return buff.toString();
    }

    public static String deleteSql(String name, String[] idColumnNames) {
        StringBuffer buff = new StringBuffer();
        buff.append("DELETE FROM ");
        buff.append(name);
        buff.append(" WHERE ");
        StringUtil.append(buff, idColumnNames, true, "=?", " AND ");
        return buff.toString();
    }

    public static String createTableSql(String name, String[] columnNames, String[] columnSqlTypes) {
        StringBuffer buff = new StringBuffer();
        buff.append("CREATE TABLE ");
        buff.append(name.toLowerCase());
        buff.append("(");
        for (int i = 0; i < columnNames.length; i++) {
            if (i > 0) {
                buff.append(",");
            }
            buff.append(columnNames[i]);
            buff.append(" ");
            buff.append(columnSqlTypes[i]);
        }
        buff.append(")");
        return buff.toString();
    }

    public static void exec(Connection conn, String sql) throws SQLException {
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.execute();
        } finally {
            close(pstmt);
        }
    }

//    public static Long getMax(Table table, String columnName) throws SQLException {
//        Connection conn = DBPower.getConnection(table.getId());
//        Statement stmt = null;
//        ResultSet rs = null;
//        try {
//            Long max = 0L;
//            String sql = "select MAX( " + columnName + " ) from " + table.getName();
//            stmt = conn.createStatement();
//            rs = stmt.executeQuery(sql);
//            if (rs.next()) {
//                max = rs.getLong(1);
//            } else {
//                max = 0L;
//            }
//            return max;
//        } finally {
//            DBUtil.close(rs, stmt, conn);
//        }
//    }

    public static Long getMax(String tableName, String columnName) throws SQLException {
        Connection conn = DBPower.getDefaultConnection();
        Statement stmt = null;
        ResultSet rs = null;
        try {
            Long max;
            String sql = "select MAX( " + columnName + " ) from " + tableName;
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            if (rs.next()) {
                max = rs.getLong(1);
            } else {
                max = 0L;
            }
            return max;
        } finally {
            DBUtil.close(rs, stmt, conn);
        }
    }

    // Sequence table name(save sequence value in table)
    private static final String SAFE_SEQUNCE = "safe_sequence";

    /**
     * 返回给定列中指定列的下一个序列值。
     * @param seqName
     * @return
     * @throws SQLException
     */
    public static Long getSequenceNextVal(String seqName) throws SQLException {
        Long currentValue = readSequenceCurrentValFromDatabase(seqName);
        Long nextValue = currentValue.longValue() + 1;
        synchronizeIntoDatabase(seqName, nextValue);

        if(nextValue != null)
            return nextValue;
        return 0L;
    }

    /**
     * 返回给定列中指定列的下一个序列值。
     * @param tableName
     * @param columnName
     * @return
     * @throws SQLException
     */
    public static Long getSequenceNextVal(String tableName, String columnName) throws SQLException {
        Long currentValue = readSequenceCurrentValFromDatabase(tableName, columnName);
        Long nextValue = currentValue.longValue() + 1;
        synchronizeIntoDatabase(tableName, columnName, nextValue);

        return nextValue;
    }

    private static void synchronizeIntoDatabase(String tableName, String columnName, Long synValue) throws SQLException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
//            conn = DBPower.getConnection(table.getId());
            conn = DBPower.getDefaultConnection();

            pstmt = conn.prepareStatement("update " + SAFE_SEQUNCE + " set current_value=? where name=?");
            String name = tableName + "_" + columnName;
            pstmt.setLong(1, synValue);
            pstmt.setString(2, name);
            pstmt.executeUpdate();
        } finally {
            DBUtil.close(pstmt, conn);
        }
    }

    private static void synchronizeIntoDatabase(String seqName, Long synValue) throws SQLException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = DBPower.getDefaultConnection();

            pstmt = conn.prepareStatement("update " + SAFE_SEQUNCE + " set current_value=? where name=?");
            pstmt.setLong(1, synValue);
            pstmt.setString(2, seqName);
            pstmt.executeUpdate();
        } finally {
            DBUtil.close(pstmt, conn);
        }
    }

    private static Long readSequenceCurrentValFromDatabase(String seqName) throws SQLException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = DBPower.getDefaultConnection();

            Long currentValue = null;
            pstmt = conn.prepareStatement("select current_value from " + SAFE_SEQUNCE + " where name=?");
            pstmt.setString(1, seqName);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                currentValue = rs.getLong(1);
            }

            if (currentValue == null) {
                DBUtil.exec(conn, "insert into " + SAFE_SEQUNCE + "(current_value,name) values(" + 0 + ",'" + seqName + "')");
                return new Long(0);
            }
            return currentValue;
        } finally {
            DBUtil.close(rs, pstmt, conn);
        }
    }

    private static Long readSequenceCurrentValFromDatabase(String tableName, String columnName) throws SQLException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = DBPower.getDefaultConnection();

            Long currentValue = null;
            pstmt = conn.prepareStatement("select current_value from " + SAFE_SEQUNCE + " where name=?");
            String name = tableName + "_" + columnName;
            pstmt.setString(1, name);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                currentValue = rs.getLong(1);
            }

            if (currentValue == null) {
                // there's no record in sequence table, then create a record
                long max = getMax(tableName, columnName);

                if (max < 0) {
                    max = 0;
                }

                DBUtil.exec(conn, "insert into " + SAFE_SEQUNCE + "(current_value,name) values(" + max + ",'" + name + "')");
                return max;
            }

            return currentValue;
        } finally {
            DBUtil.close(rs, pstmt, conn);
        }
    }

    public static String roleTableCreateSql(String appName) {
        String sqlRole = " CREATE TABLE " + appName.toLowerCase() + "_role ( id integer NOT NULL,	name varchar(100) NOT NULL UNIQUE, " + "description varchar(500), PRIMARY KEY(id) ) ";
        return sqlRole;
    }

    public static String rolePrivilegeTableCreateSql(String appName) {
        String sqlRolePrivilege = " CREATE TABLE " + appName.toLowerCase() + "_roleprivilege (	roleid integer NOT NULL,	privilegeid integer NOT NULL, " + "PRIMARY KEY(roleid, privilegeid), FOREIGN KEY(roleid) REFERENCES " + appName + "_role (id), FOREIGN KEY(privilegeid) REFERENCES " + appName + "_privilege (id)) ";
        return sqlRolePrivilege;
    }

    public static String privilegeTableCreateSql(String appName) {
        String sqlPrivilege = " CREATE TABLE " + appName.toLowerCase() + "_privilege ( id integer NOT NULL, pid integer, " + "description varchar(500), name varchar(100) NOT NULL UNIQUE, " + "isLeaf integer, display integer, decisionPolicyCombAlg integer, " + "queryPolicyCombAlg integer, type integer, constantName varchar(40), " + "url varchar(100), target varchar(20), orderNum integer, PRIMARY KEY(id) ) ";
        return sqlPrivilege;
    }

    public static String userRoleTableCreateSql(String appName, String userTypeName, String idColumnName, String idColumnType) {
        String tableName = appName + "_" + userTypeName + "_userrole";
        String sqlUserRole = "CREATE TABLE " + tableName.toLowerCase() + " ( userid " + idColumnType + " NOT NULL, roleid integer NOT NULL, PRIMARY KEY(userid, roleid), " + "FOREIGN KEY(roleid) REFERENCES " + appName + "_role (id)) ";
        return sqlUserRole;
    }

    public static String roleTableDropSql(String appName) {
        String sqlRole = " DROP TABLE " + appName + "_role";
        return sqlRole;
    }

    public static String rolePrivilegeTableDropSql(String appName) {
        String sqlRolePrivilege = " DROP TABLE " + appName + "_roleprivilege";
        return sqlRolePrivilege;
    }

    public static String privilegeTableDropSql(String appName) {
        String sqlPrivilege = " DROP TABLE " + appName + "_privilege";
        return sqlPrivilege;
    }

    public static String userRoleTableDropSql(String appName, String userTypeName) {
        String tableName = appName + "_" + userTypeName + "_userrole";
        String sqlUserRole = "DROP TABLE " + tableName;
        return sqlUserRole;
    }

    public static String tableQueryCreateSql(String appName) {
        return "CREATE TABLE " + appName.toLowerCase() + "_query ( id int NOT NULL,name varchar(100) NOT NULL UNIQUE, " + "description varchar(500)," + "installDate date, " + "fileName varchar(40),pid int, isLeaf int," + "PRIMARY KEY(id) ) ";
    }

    public static String tableQueryDropSql(String appName) {
        return "DROP TABLE " + appName + "_query";
    }

    public static String tableUserCategoryCreateSql(String appName) {
        return "CREATE TABLE " + appName.toLowerCase() + "_usercategory ( id int NOT NULL, name varchar(100) NOT NULL UNIQUE, " + "description varchar(500), " + "installDate date, " + "fileName varchar(40),pid int, isLeaf int," + "PRIMARY KEY(id) ) ";
    }

    public static String tableUserCategoryDropSql(String appName) {
        return "DROP TABLE " + appName + "_usercategory";
    }

    public static String tableDecisionEntitlementCreateSql(String appName) {
        return "create table " + appName.toLowerCase() + "_decision_entitlement(" + "id int NOT NULL," + "privilegeId int," + "userCategoryId int," + "businessDataId int," + "effect varchar(100)," + "denyReason varchar(1000)," + "PRIMARY KEY(id) )";
    }

    public static String tableDecisionEntitlementDropSql(String appName) {
        return "DROP TABLE " + appName + "_decision_entitlement";
    }

    public static String tableQueryEntitlementCreateSql(String appName) {
        return "create table " + appName.toLowerCase() + "_query_entitlement(" + "id int NOT NULL," + "privilegeId int," + "userCategoryId int," + "queryId int," + "description varchar(500)," + "PRIMARY KEY(id) )";
    }

    public static String tableQueryEntitlementDropSql(String appName) {
        return "DROP TABLE " + appName + "_query_entitlement";
    }

    public static String tableBusinessDataCreateSql(String appName) {
        return "CREATE TABLE " + appName.toLowerCase() + "_businessdata ( id int NOT NULL,name varchar(100) NOT NULL UNIQUE, " + "description varchar(500)," + "installDate date, " + "fileName varchar(40),pid int, isLeaf int,	" + "PRIMARY KEY(id) ) ";
    }

    public static String tableBusinessDataDropSql(String appName) {
        return "DROP TABLE " + appName + "_businessdata";
    }

    public static String tableBackUpCreateSql(String appName) {
        return "CREATE TABLE " + appName.toLowerCase() + "_backup ( id int NOT NULL,createTime timestamp, " + "description varchar(500),content blob," + "PRIMARY KEY(id) ) ";
    }

    public static String tableBackupDropSql(String appName) {
        return "DROP TABLE " + appName + "_backup";
    }

    public static String getLimitString(Connection conn, String sql, int first, int max) {
        String dataBase = getDatabaseProductName(conn);
        String result = sql;
        if (dataBase.equals(DB2)) {
            result = getDB2LimitString(sql, first, max);
        } else if (dataBase.equals(ORACLE)) {
            result = getOracleLimitString(sql, first, max);
        } else if (dataBase.equals(MYSQL)) {
            result = getMySQLLimitString(sql, first, max);
        }

        if (log.isDebugEnabled()) {
            log.debug("\n" + result);
        }

        return result;
    }

    private static String getMySQLLimitString(String sql, int first, int max) {
        return new StringBuffer(sql.length() + 20).append(sql).append(" limit " + first + ", " + max).toString();
    }

    private static String getOracleLimitString(String sql, int first, int max) {

        sql = sql.trim();
        boolean isForUpdate = false;
        if (sql.toLowerCase().endsWith(" for update")) {
            sql = sql.substring(0, sql.length() - 11);
            isForUpdate = true;
        }

        StringBuffer pagingSelect = new StringBuffer(sql.length() + 100);

        pagingSelect.append("select * from ( select row_.*, rownum rownum_ from ( ");

        pagingSelect.append(sql);

        // hibernate style
        // pagingSelect.append(" ) row_ ) where rownum_ <= " + (first + max)
        // + " and rownum_ > " + first);

        // better style
        pagingSelect.append(" ) row_ where rownum <= " + (first + max) + ") where " + " rownum_ >= " + (first + 1));

        if (isForUpdate) {
            pagingSelect.append(" for update");
        }

        return pagingSelect.toString();
    }

    private static String getDB2LimitString(String sql, int first, int max) {

        int startOfSelect = sql.toLowerCase().indexOf("select");

        StringBuffer pagingSelect = new StringBuffer(sql.length() + 100).append(sql.substring(0, startOfSelect)) // add
                // the
                // comment
                .append("select * from ( select ") // nest the main query in an
                // outer select
                .append(getRowNumber(sql)); // add the rownnumber bit into the
        // outer query select list

        if (hasDistinct(sql)) {
            pagingSelect.append(" row_.* from ( ") // add another (inner) nested
                    // select
                    .append(sql.substring(startOfSelect)) // add the main query
                    .append(" ) as row_"); // close off the inner nested select
        } else {
            pagingSelect.append(sql.substring(startOfSelect + 6)); // add the
            // main
            // query
        }

        pagingSelect.append(" ) as temp_ where rownumber_ ");

        // add the restriction to the outer select
        pagingSelect.append("between " + (first + 1) + " and " + (first + max));

        return pagingSelect.toString();
    }

    private static boolean hasDistinct(String sql) {
        return sql.toLowerCase().indexOf("select distinct") >= 0;
    }

    /**
     * Render the <tt>rownumber() over ( .... ) as rownumber_,</tt> bit, that
     * goes in the select list
     */
    private static String getRowNumber(String sql) {
        StringBuffer rownumber = new StringBuffer(50).append("rownumber() over(");

        int orderByIndex = sql.toLowerCase().indexOf("order by");

        if (orderByIndex > 0 && !hasDistinct(sql)) {
            rownumber.append(sql.substring(orderByIndex));
        }

        rownumber.append(") as rownumber_,");

        return rownumber.toString();
    }

    public static String getDatabaseProductName(Connection conn) {
        String dataBase;
        try {
            dataBase = conn.getMetaData().getDatabaseProductName();
            dataBase = dataBase.toUpperCase();
        } catch (SQLException e) {
            throw new AuthException(e);
        }

        if (dataBase.indexOf(DB2) >= 0) {
            return DB2;
        } else if (dataBase.indexOf(ORACLE) >= 0) {
            return ORACLE;
        } else if (dataBase.indexOf(MYSQL) >= 0) {
            return MYSQL;
        } else if (dataBase.indexOf(SQLSERVER) >= 0) {
            return SQLSERVER;
        } else {
            return OTHER_DATABASE;
        }
    }

    public static String getDefaultSchema(Connection conn) throws SQLException {
        String productName = getDatabaseProductName(conn);
        if (productName.equals(MYSQL) || productName.equals(SQLSERVER)) {
            return conn.getCatalog();
        } else {
            DatabaseMetaData metaData = conn.getMetaData();
            return metaData.getUserName();
        }
    }

    public static void setCommitMode(Connection conn, boolean autoCommit) {
        if (conn != null) {
            try {
                conn.setAutoCommit(autoCommit);
            } catch (SQLException e) {
            }
        }
    }

    public static void rollback(Connection conn) {
        if (conn != null) {
            try {
                conn.rollback();
            } catch (SQLException e) {
            }
        }
    }

}
